﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TalentPools.Model;
namespace TalentPools.DAL
{
    public class alertResumeService
    {
        #region 根据ID更新简历信息
        /// <summary>
        /// 根据ID更新简历信息
        /// </summary>
        /// <param name="re"></param>
        /// <returns></returns>
        public bool updatResumeByid(resume re)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update resume set ");
            strSql.Append("name=@name,");
            strSql.Append("sex=@sex,");
            strSql.Append("birthday=@birthday,");
            strSql.Append("marriage=@marriage,");
            strSql.Append("card=@card,");
            strSql.Append("politicsStatus=@politicsStatus,");
            strSql.Append("HKPlace=@HKPlace,");
            strSql.Append("city=@city,");
            strSql.Append("currentAddress=@currentAddress,");
            strSql.Append("zipCode=@zipCode,");
            strSql.Append("cellphone=@cellphone,");
            strSql.Append("phoneCode=@phoneCode,");
            strSql.Append("email=@email,");
            strSql.Append("homePage=@homePage,");
            strSql.Append("workYear=@workYear,");
            strSql.Append("lastUpdateTime=@lastUpdateTime,");
            strSql.Append("isPublic=@ispublic");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id",re.Id),
					new SqlParameter("@name", re.name),
					new SqlParameter("@sex",re.sex),
					new SqlParameter("@birthday",re.birthday),
					new SqlParameter("@marriage", re.marriage),
					new SqlParameter("@card",re.card),
					new SqlParameter("@politicsStatus", re.politicsStatus),
					new SqlParameter("@HKPlace", re.HKPlace),
					new SqlParameter("@city",re.city),
					new SqlParameter("@currentAddress", re.currentAddress),
					new SqlParameter("@zipCode",re.zipCode),
					new SqlParameter("@cellphone", re.cellphone),
					new SqlParameter("@phoneCode", re.phoneCode),
					new SqlParameter("@email", re.email),
					new SqlParameter("@homePage", re.homePage),
                    new SqlParameter("@workYear",re.workYear),
                    new SqlParameter("@lastUpdateTime",re.lastUpdateTime),
                    new SqlParameter("@ispublic",re.isPublic)
     };
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        #endregion
        #region 查看自己公开的简历数
        /// <summary>
        /// 查看自己公开的简历数
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns></returns>
        public int getpubliccount(string id)
        {
            string sql = "select COUNT(*) from resume where userId=@id and isPublic=1 ";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, par);

        }
        #endregion
        #region 根据简历Id显示基本详细信息
        public List<resume> GetResumeId(int resumeId)
        {
            string sql = "select * from resume where id=@resumeId";
            SqlParameter para = new SqlParameter("@resumeId", resumeId);
            List<resume> list = new List<resume>();
            using (SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, para))
            {
                while (read.Read())
                {
                    resume re = new resume();
                    re.resumeName = read["resumeName"].ToString();
                    re.Id = Convert.ToInt32(read["id"]);
                    re.username = read["username"].ToString();
                    re.name = read["name"].ToString();
                    re.sex = read["sex"].ToString();
                    re.birthday = Convert.ToDateTime(read["birthday"]);
                    re.marriage = read["marriage"].ToString();
                    re.card = read["card"].ToString();
                    re.politicsStatus = read["politicsStatus"].ToString();
                    re.HKPlace = read["HKPlace"].ToString();
                    re.city = read["city"].ToString();
                    re.currentAddress = read["currentAddress"].ToString();
                    re.zipCode = read["zipCode"].ToString();
                    re.cellphone = read["cellphone"].ToString();
                    re.phoneCode = read["phoneCode"].ToString();
                    re.email = read["email"].ToString();
                    re.homePage = read["homePage"].ToString();
                    re.isPublic = Convert.ToBoolean(read["isPublic"]);
                    re.workYear = read["workYear"].ToString();
                    list.Add(re);
                }
                return list;
            }
        }
        #endregion
        #region 根据简历Id显示求职意向表的详细信息
        /// <summary>
        /// 根据简历Id显示求职意向表的详细信息
        /// </summary>
        /// <param name="id">简历ID</param>
        /// <returns></returns>
        public List<intention> getintentionByid(int id)
        {
            List<intention> list = new List<intention>();
            string sql = "select * from intention where resumeId=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, par);
            while (read.Read())
            {
                intention inte = new intention();
                inte.id = read.GetInt32(0);
                inte.resumeId = read.GetInt32(1);
                inte.jobCategory = read.GetString(2);
                inte.area = read.GetString(3);
                inte.profession = read.GetString(4);
                inte.industry = read.GetString(5);
                inte.pay = read.GetString(6);
                inte.state = read.GetString(7);
                list.Add(inte);
            }
            return list;
        }
        #endregion
        #region 根据简历ID找出相关的求职信息表的数目
        /// <summary>
        /// 根据简历ID找出相关的求职信息表的数目
        /// </summary>
        /// <param name="id">简历</param>
        /// <returns></returns>
        public int getintecountByid(string id)
        {
            string sql = "select count(*) from intention where resumeId=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, par);
        }
        #endregion
        #region 根据简历Id更新求职信息表的信息
        /// <summary>
        /// 根据简历Id更新求职信息表的信息
        /// </summary>
        /// <param name="inte"></param>
        /// <returns></returns>
        public bool updatinteByid(intention inte)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update intention set ");
            strSql.Append("jobCategory=@jobCategory,");
            strSql.Append("area=@area,");
            strSql.Append("profession=@profession,");
            strSql.Append("industry=@industry,");
            strSql.Append("pay=@pay,");
            strSql.Append("state=@state");
            strSql.Append(" where resumeId=@resumeId ");
            SqlParameter[] parameters ={
					new SqlParameter("@resumeId", inte.resumeId),
					new SqlParameter("@jobCategory", inte.jobCategory),
					new SqlParameter("@area",inte.area),
					new SqlParameter("@profession", inte.profession),
					new SqlParameter("@industry", inte.industry),
					new SqlParameter("@pay", inte.pay),
					new SqlParameter("@state", inte.state)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;

        }
        #endregion
        #region 向求职意向表插入新的数据
        /// <summary>
        /// 向求职意向表插入新的数据
        /// </summary>
        /// <param name="inte"></param>
        /// <returns></returns>
        public bool inertinte(intention inte)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into intention(");
            strSql.Append("resumeId,jobCategory,area,profession,industry,pay,state)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@jobCategory,@area,@profession,@industry,@pay,@state)");
            SqlParameter[] parameters = {
					new SqlParameter("@resumeId", inte.resumeId),
					new SqlParameter("@jobCategory",inte.jobCategory),
					new SqlParameter("@area", inte.area),
					new SqlParameter("@profession", inte.profession),
					new SqlParameter("@industry", inte.industry),
					new SqlParameter("@pay",inte.pay ),
					new SqlParameter("@state",inte.state )};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;

        }
        #endregion

        #region 根据简历ID找出相关的培训表的数目
        /// <summary>
        /// 根据简历ID找出相关的培训表的数目
        /// </summary>
        /// <param name="tra"></param>
        /// <returns></returns>
        public int gettraincountByid(string id)
        {
            string sql = "select count(*) from training where resumeId=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, par);
        }

        #endregion
        #region 根据简历ID查找培训表的详细信息
        /// <summary>
        /// 根据简历ID查找培训表的详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<training> gettraningByid(string id)
        {
            List<training> list = new List<training>();
            string sql = "select * from training where resumeId=@id ";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, par);
            while (read.Read())
            {
                training tra = new training();
                tra.tittle = read.GetString(2);
                tra.beginTime = read.GetDateTime(3);
                tra.endTime = read.GetDateTime(4);
                tra.result = read.GetString(5);
                tra.details = read.GetString(6);
                list.Add(tra);
            }
            return list;
        }
        #endregion
        #region 更新培训表的信息
        /// <summary>
        /// 更新培训表的信息
        /// </summary>
        /// <param name="tra"></param>
        /// <returns></returns>
        public bool updateTraning(training tra)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update training set ");
            strSql.Append("tittle=@tittle,");
            strSql.Append("beginTime=@beginTime,");
            strSql.Append("endTime=@endTime,");
            strSql.Append("result=@result,");
            strSql.Append("details=@details");
            strSql.Append(" where trainingId=@trainingId ");
            SqlParameter[] parameters = {
					new SqlParameter("@trainingId",tra.trainingId),
					new SqlParameter("@tittle", tra.tittle),
					new SqlParameter("@beginTime",tra.beginTime),
					new SqlParameter("@endTime",tra.endTime),
					new SqlParameter("@result",tra.result),
					new SqlParameter("@details",tra.details)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;

        }
        #endregion
        #region 向培训经历表中添加新的数据
        /// <summary>
        /// 向培训经历表中添加新的数据
        /// </summary>
        /// <param name="tra"></param>
        /// <returns></returns>
        public bool inserttra(training tra)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into training(");
            strSql.Append("resumeId,tittle,beginTime,endTime,result,details)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@tittle,@beginTime,@endTime,@result,@details)");
            SqlParameter[] parameters ={
					new SqlParameter("@resumeId",tra.resumeId),
					new SqlParameter("@tittle", tra.tittle),
					new SqlParameter("@beginTime",tra.beginTime),
					new SqlParameter("@endTime",tra.endTime),
					new SqlParameter("@result", tra.result),
					new SqlParameter("@details",tra.details )};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 根据简历ID查找相关工作表的数目
        /// <summary>
        /// 根据简历ID查找相关工作表的数目
        /// </summary>
        /// <param name="id">简历ID</param>
        /// <returns></returns>
        public int getrecorcountByid(string id)
        {
            string sql = "select count(*) from record where resumeId=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, par);

        }
        #endregion
        #region 根据简历ID工作表的详细信息
        /// <summary>
        /// 根据简历ID工作表的详细信息
        /// </summary>
        /// <param name="id">简历ID</param>
        /// <returns></returns>
        public List<record> getrecordByid(string id)
        {
            List<record> list = new List<record>();
            string sql = "select * from record where resumeId=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, par);
            while (read.Read())
            {
                record rec = new record();
                rec.title = read.GetString(2);
                rec.beginTime = read.GetDateTime(3);
                rec.endTime = read.GetDateTime(4);
                rec.content = read.GetString(5);
                rec.details = read.GetString(6);
                list.Add(rec);
            }
            return list;
        }
        #endregion
        #region 向工作经历表中添加数据
        /// <summary>
        /// 向工作经历表中添加数据
        /// </summary>
        /// <param name="rec"></param>
        /// <returns></returns>
        public bool insertrecord(record rec)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into record(");
            strSql.Append("resumeId,title,beginTime,endTime,content,details)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@title,@beginTime,@endTime,@content,@details)");
            SqlParameter[] parameters = {
					new SqlParameter("@resumeId", rec.resumeId),
					new SqlParameter("@title", rec.title),
					new SqlParameter("@beginTime",rec.beginTime),
					new SqlParameter("@endTime", rec.endTime),
					new SqlParameter("@content",rec.content),
					new SqlParameter("@details",rec.details )};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;

        }
        #endregion
        #region 更新工作经历表
        /// <summary>
        /// 更新工作经历表
        /// </summary>
        /// <param name="rec"></param>
        /// <returns></returns>
        public bool updaterec(record rec)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update record set ");
            strSql.Append("title=@title,");
            strSql.Append("beginTime=@beginTime,");
            strSql.Append("endTime=@endTime,");
            strSql.Append("content=@content,");
            strSql.Append("details=@details");
            strSql.Append(" where recordid=@recordid ");
            SqlParameter[] parameters = {
					new SqlParameter("@recordid", rec.recordid),
					new SqlParameter("@title", rec.title),
					new SqlParameter("@beginTime", rec.beginTime),
					new SqlParameter("@endTime", rec.endTime),
					new SqlParameter("@content",rec.content),
					new SqlParameter("@details",rec.details )};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 根据简历ID查找相关自定义表的数目
        /// <summary>
        /// 根据简历ID查找相关自定义表的数目
        /// </summary>
        /// <param name="id">简历ID</param>
        /// <returns></returns>
        public int getcustocountByid(string id)
        {
            string sql = "select count(*) from custom where resumeId=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, par);
        }
        #endregion
        #region 根据简历ID查找自定义表的详细信息
        /// <summary>
        /// 根据简历ID查找自定义表的详细信息
        /// </summary>
        /// <param name="id">简历ID</param>
        /// <returns></returns>
        public List<custom> getcustomeByid(string id)
        {
            List<custom> list = new List<custom>();
            string sql = "select * from custom where resumeId=@id ";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, par);
            while (read.Read())
            {
                custom cus = new custom();
                cus.tittle = read.GetString(2);
                cus.content = read.GetString(3);
                list.Add(cus);
            }
            return list;
        }
        #endregion
        #region 向自定义表中添加数据
        /// <summary>
        /// 向自定义表中添加数据
        /// </summary>
        /// <param name="cus"></param>
        /// <returns></returns>
        public bool insertcustom(custom cus)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into custom(");
            strSql.Append("resumeId,tittle,content)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@tittle,@content)");
            SqlParameter[] parameters = {
					new SqlParameter("@resumeId", cus.resumeId),
					new SqlParameter("@tittle", cus.tittle),
					new SqlParameter("@content",cus.content)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;


        }
        #endregion
        #region 更新自定义表
        /// <summary>
        /// 更新自定义表
        /// </summary>
        /// <param name="cus"></param>
        /// <returns></returns>
        public bool updatecustom(custom cus)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update custom set ");
            strSql.Append("tittle=@tittle,");
            strSql.Append("content=@content");
            strSql.Append(" where resumeId=@resumeId ");
            SqlParameter[] parameters = {
					new SqlParameter("@resumeId", cus.resumeId),
					new SqlParameter("@tittle",cus.tittle),
					new SqlParameter("@content",cus.content)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        #endregion
        #region 更新简历状态
        /// <summary>
        /// 更改简历推荐状态
        /// </summary>
        /// <param name="resumeId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool UpdateResumeState(int resumeId, bool state)
        {
            string sql = "update resume set isTj=@state where id=@resumeId";
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@state",state),
            new SqlParameter("@resumeId",resumeId)
            };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0;
        }
        /// <summary>
        /// 更改简历屏蔽状态
        /// </summary>
        /// <param name="resumeId"></param>
        /// <param name="isPb"></param>
        /// <returns></returns>
        public bool UpdateResumeState1(int resumeId, bool isPb)
        {
            string sql = "update resume set isPb=@isPb where id=@resumeId";
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@isPb",isPb),
            new SqlParameter("@resumeId",resumeId)
            };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0;
        }
        #endregion

    }
}

